package com.infore.statisticsAndAlarm.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.statisticsAndAlarm.entity.YearData;

@Repository
public interface IYearDataRepository extends JpaRepository<YearData, String>,JpaSpecificationExecutor<YearData>{

	List<YearData> findByStationCodeInAndFactorCodeInAndYearInOrderByYearAsc(List<String> stationCode,List<String> factorCode,List<String> year);

	List<YearData> findByStationCodeInAndFactorCodeInAndYearBetweenOrderByYearAsc(List<String> stationCode,List<String> factorCode,String startYear,String endYear);

	//通过站点，因子，时间段，得到每一条记录,返回listMap
	@Query(value = "select t.station_code as MN, t.factor_code as CODE , to_char(avg(t.data_value_avg),'fm999999990.999999999') as VALUE,to_char(sum(t.real_num)) as COUNT "
			+ "from year_data t where (t.station_code in (:mnList)) and (t.factor_code in (:projectCodes)) and (t.year between :startTime and :endTime) group by t.station_code,t.factor_code order by t.station_code",nativeQuery = true)
	List<Map<String, Object>> audioMoreReport(@Param("startTime") String startTime,@Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes,@Param("mnList") List<String> mnList);

	//通过站点，因子，时间段，得到每一条记录,返回listMap
	@Query(value = "select t.year as TIME,t.station_code as MN, t.factor_code as CODE , t.data_value_avg as VALUE "
			+ "from year_data t where (t.station_code in (:mnList)) and (t.factor_code in (:projectCodes)) "
			+ "and (t.year between :startTime and :endTime) "
			+ "order by t.station_code",nativeQuery = true)
	List<Map<String, Object>> audioSingleReport(@Param("startTime") String startTime,@Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes,@Param("mnList") List<String> mnList);

}
